package dao.gradedao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.DateTool;
import util.SHAUtil;
import dao.basedao.DBManager;
import model.GradeRecord;
import model.Work;

public class GradeDaoImpl implements GradeDao{

	@Override
	public void addGrade(GradeRecord grade) throws Exception {
		Connection con=DBManager.getConn();
		PreparedStatement ps=con.prepareStatement("insert into grade(work_id,stu_id,grade,schoolyear) values(?,?,?,?)");
		ps.setInt(1, grade.getWork_id());
		ps.setString(2,grade.getStu_id());
		ps.setInt(3,grade.getGrade());
		ps.setString(4, grade.getSchoolyear());
		ps.executeUpdate();
		ps.close();
		DBManager.closeConn(con);
	}

	@Override
	public int queryGrade(int work_id,String stu_id,String schoolyear) throws Exception {
		int grade=0;
		Connection con=DBManager.getConn();
		PreparedStatement ps=con.prepareStatement("select grade from grade where work_id=? and stu_id=? and schoolyear=?");
		ps.setInt(1, work_id);
		ps.setString(2,stu_id);
		ps.setString(3,schoolyear);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			grade=rs.getInt("grade");
		}
		ps.close();
		DBManager.closeConn(con);
		return grade;
	}

	@Override
	public ArrayList<GradeRecord> queryAllGrade(String stu_id, String schoolyear)
			throws Exception {
		ArrayList<GradeRecord> list=new ArrayList<GradeRecord>();
		Connection con=DBManager.getConn();
		PreparedStatement ps=con.prepareStatement("select * from grade where stu_id=? and schoolyear=?");
		ps.setString(1,stu_id);
		ps.setString(2,schoolyear);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			GradeRecord record=new GradeRecord();
			record.setWork_id(rs.getInt("work_id"));
			record.setStu_id(rs.getString("stu_id"));
			record.setSchoolyear(rs.getString("schoolyear"));
			record.setGrade(rs.getInt("grade"));
			list.add(record);
		}
		ps.close();
		DBManager.closeConn(con);
		return list;
	}

}
